
// =================================================================== 
// class description : 
// =================================================================== 
// File: UserDBHelper.cs
// Copyright  @ AC
// History:
// 		2012/7/26  created by 
// 
// =================================================================== 

using System;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using InternalManagementSystem.DAO.Models;

namespace InternalManagementSystem.DAO
{
    public class StaffDAO : DataAccessObject
    {
        /// <summary>
        ///Description   :gets a User record from db
        ///				  package , should be implicly downcast
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : User object. Will be null if record not found.
        /// </summary>
        public Staff LoadUser(System.Int32 userID)
        {
            string sqlSelect = "SELECT  UserID,  UserName,  CompanyID,  Sex,  WorkType,  Credential,  IDCard,  IsSafetyEducation,  IsAutoDeductPoints,  AutoDeductPeriod,  AutoDeductPoints,  AutoDeductDate,  IsAutoRecharge,  CurrentIntegral,  RoleID,  UserTypeID,  Password ,IsFine FROM Staff WHERE  UserID = @UserID ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            Staff pUser = null;

            //SqlCommand sqlCmd = new SqlCommand("usSelectUser");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and close the connection. Should be <=1 results.
                if (dr.Read())
                {
                    pUser = new Staff();
                    pUser.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    pUser.UserName = (dr["UserName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["UserName"]);
                    pUser.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    pUser.Sex = (dr["Sex"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Sex"]);
                    pUser.WorkType = (dr["WorkType"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["WorkType"]);
                    pUser.Credential = (dr["Credential"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Credential"]);
                    pUser.IDCard = (dr["IDCard"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["IDCard"]);
                    pUser.IsSafetyEducation = (dr["IsSafetyEducation"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsSafetyEducation"]);
                    pUser.IsAutoDeductPoints = (dr["IsAutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoDeductPoints"]);
                    pUser.AutoDeductPeriod = (dr["AutoDeductPeriod"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPeriod"]);
                    pUser.AutoDeductPoints = (dr["AutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPoints"]);
                    pUser.AutoDeductDate = (dr["AutoDeductDate"] == DBNull.Value) ? SqlDateTime.MinValue.Value : (DateTime)(dr["AutoDeductDate"]);
                    pUser.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    pUser.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    pUser.RoleID = (dr["RoleID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["RoleID"]);
                    pUser.IsFine = (dr["IsFine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsFine"]);
                    pUser.UserTypeID = (dr["UserTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserTypeID"]);
                    pUser.Password = (dr["Password"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Password"]);
                }

                conn.Close();

            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pUser;
        }

        /// <summary>
        ///Description   :gets a User collection object of  all
        ///				  User records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : User collection object.
        /// </summary>
        public UserCollection LoadAllUsers()
        {
            string sqlSelect = "SELECT  UserID,  UserName,  CompanyID,  Sex,  WorkType,  Credential,  IDCard,  IsSafetyEducation,  IsAutoDeductPoints,  AutoDeductPeriod,  AutoDeductPoints,  AutoDeductDate,  IsAutoRecharge,  CurrentIntegral,  RoleID,  UserTypeID,  Password,IsFine  FROM Staff";
            SqlConnection conn = new SqlConnection(ConnectionString);
            UserCollection pUserCollection = new UserCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectUserAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    Staff vo = new Staff();
                    vo.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    vo.UserName = (dr["UserName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["UserName"]);
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.Sex = (dr["Sex"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Sex"]);
                    vo.WorkType = (dr["WorkType"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["WorkType"]);
                    vo.Credential = (dr["Credential"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Credential"]);
                    vo.IDCard = (dr["IDCard"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["IDCard"]);
                    vo.IsSafetyEducation = (dr["IsSafetyEducation"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsSafetyEducation"]);
                    vo.IsAutoDeductPoints = (dr["IsAutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoDeductPoints"]);
                    vo.AutoDeductPeriod = (dr["AutoDeductPeriod"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPeriod"]);
                    vo.AutoDeductPoints = (dr["AutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPoints"]);
                    vo.AutoDeductDate = (dr["AutoDeductDate"] == DBNull.Value) ? SqlDateTime.MinValue.Value : (DateTime)(dr["AutoDeductDate"]);
                    vo.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    vo.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    vo.RoleID = (dr["RoleID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["RoleID"]);
                    vo.UserTypeID = (dr["UserTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserTypeID"]);
                    vo.Password = (dr["Password"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Password"]);
                    vo.IsFine = (dr["IsFine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsFine"]);
                    
                    pUserCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pUserCollection;
        }

        /// <summary>
        ///Description   :gets a User collection object of  all
        ///				  User records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : User collection object.
        /// </summary>
        public UserCollection LoadAllUsers(string filter, string sort)
        {
            string sqlSelect = "SELECT  UserID,  UserName,  CompanyID,  Sex,  WorkType,  Credential,  IDCard,  IsSafetyEducation,  IsAutoDeductPoints,  AutoDeductPeriod,  AutoDeductPoints,  AutoDeductDate,  IsAutoRecharge,  CurrentIntegral,  RoleID,  UserTypeID,  Password,IsFine  FROM Staff ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);
            UserCollection pUserCollection = new UserCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectUserDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    Staff vo = new Staff();
                    vo.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    vo.UserName = (dr["UserName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["UserName"]);
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.Sex = (dr["Sex"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Sex"]);
                    vo.WorkType = (dr["WorkType"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["WorkType"]);
                    vo.Credential = (dr["Credential"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Credential"]);
                    vo.IDCard = (dr["IDCard"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["IDCard"]);
                    vo.IsSafetyEducation = (dr["IsSafetyEducation"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsSafetyEducation"]);
                    vo.IsAutoDeductPoints = (dr["IsAutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoDeductPoints"]);
                    vo.AutoDeductPeriod = (dr["AutoDeductPeriod"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPeriod"]);
                    vo.AutoDeductPoints = (dr["AutoDeductPoints"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["AutoDeductPoints"]);
                    vo.AutoDeductDate = (dr["AutoDeductDate"] == DBNull.Value) ? SqlDateTime.MinValue.Value : (DateTime)(dr["AutoDeductDate"]);
                    vo.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    vo.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    vo.RoleID = (dr["RoleID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["RoleID"]);
                    vo.UserTypeID = (dr["UserTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserTypeID"]);
                    vo.Password = (dr["Password"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Password"]);
                    vo.IsFine = (dr["IsFine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsFine"]);
                    
                    pUserCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pUserCollection;
        }

        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  User records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet()
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  UserID,  UserName,  CompanyID,  Sex,  WorkType,  Credential,  IDCard,  IsSafetyEducation,  IsAutoDeductPoints,  AutoDeductPeriod,  AutoDeductPoints,  AutoDeductDate,  IsAutoRecharge,  CurrentIntegral,  RoleID,  UserTypeID,  Password,IsFine  FROM Staff";
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectUserAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  User records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet(string filter, string sort)
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  UserID,  UserName,  CompanyID,  Sex,  WorkType,  Credential,  IDCard,  IsSafetyEducation,  IsAutoDeductPoints,  AutoDeductPeriod,  AutoDeductPoints,  AutoDeductDate,  IsAutoRecharge,  CurrentIntegral,  RoleID,  UserTypeID,  Password,IsFine  FROM Staff ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectUserDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        /// <summary>
        ///Description   :updates a User record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : true - if record was update .
        //				   false - if record was not updated;
        /// </summary>
        public void UpdateUser(Staff pUser)
        {
            string sqlUpdate = "UPDATE Staff SET  UserName = @UserName, CompanyID = @CompanyID, Sex = @Sex, WorkType = @WorkType, Credential = @Credential, IDCard = @IDCard, IsSafetyEducation = @IsSafetyEducation, IsAutoDeductPoints = @IsAutoDeductPoints, AutoDeductPeriod = @AutoDeductPeriod, AutoDeductPoints = @AutoDeductPoints, AutoDeductDate = @AutoDeductDate, IsAutoRecharge = @IsAutoRecharge, CurrentIntegral = @CurrentIntegral, RoleID = @RoleID, UserTypeID = @UserTypeID, Password = @Password,IsFine=@IsFine WHERE UserID = @UserID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertUpdateUser");
            SqlCommand sqlCmd = new SqlCommand(sqlUpdate);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = pUser.UserID;
            sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = pUser.UserName;
            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pUser.CompanyID;
            sqlCmd.Parameters.Add("@Sex", SqlDbType.Int).Value = pUser.Sex;
            sqlCmd.Parameters.Add("@WorkType", SqlDbType.VarChar).Value = pUser.WorkType;
            sqlCmd.Parameters.Add("@Credential", SqlDbType.VarChar).Value = pUser.Credential;
            sqlCmd.Parameters.Add("@IDCard", SqlDbType.VarChar).Value = pUser.IDCard;
            sqlCmd.Parameters.Add("@IsSafetyEducation", SqlDbType.Int).Value = pUser.IsSafetyEducation;
            sqlCmd.Parameters.Add("@IsAutoDeductPoints", SqlDbType.Int).Value = pUser.IsAutoDeductPoints;
            sqlCmd.Parameters.Add("@AutoDeductPeriod", SqlDbType.Int).Value = pUser.AutoDeductPeriod;
            sqlCmd.Parameters.Add("@AutoDeductPoints", SqlDbType.Int).Value = pUser.AutoDeductPoints;
            sqlCmd.Parameters.Add("@AutoDeductDate", SqlDbType.DateTime).Value = pUser.AutoDeductDate;
            sqlCmd.Parameters.Add("@IsAutoRecharge", SqlDbType.Int).Value = pUser.IsAutoRecharge;
            sqlCmd.Parameters.Add("@CurrentIntegral", SqlDbType.Int).Value = pUser.CurrentIntegral;
            sqlCmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = pUser.RoleID;
            sqlCmd.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = pUser.UserTypeID;
            sqlCmd.Parameters.Add("@Password", SqlDbType.Char).Value = pUser.Password;
            sqlCmd.Parameters.Add("@IsFine", SqlDbType.Int).Value = pUser.IsFine;
            try
            {
                // open the connection
                conn.Open();

                //execute the update command
                sqlCmd.ExecuteNonQuery();

                //close the connection
                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : create a new User record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : None.
        /// </summary>
        public void InsertUser(Staff pUser)
        {
            string sqlInsert = " INSERT INTO Staff (UserName,CompanyID,Sex,WorkType,Credential,IDCard,IsSafetyEducation,IsAutoDeductPoints,AutoDeductPeriod,AutoDeductPoints,AutoDeductDate,IsAutoRecharge,CurrentIntegral,RoleID,UserTypeID,Password,IsFine) VALUES (  @UserName, @CompanyID, @Sex, @WorkType, @Credential, @IDCard, @IsSafetyEducation, @IsAutoDeductPoints, @AutoDeductPeriod, @AutoDeductPoints, @AutoDeductDate, @IsAutoRecharge, @CurrentIntegral, @RoleID, @UserTypeID, @Password,@IsFine) ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertUser");
            SqlCommand sqlCmd = new SqlCommand(sqlInsert);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = pUser.UserName;
            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pUser.CompanyID;
            sqlCmd.Parameters.Add("@Sex", SqlDbType.Int).Value = pUser.Sex;
            sqlCmd.Parameters.Add("@WorkType", SqlDbType.VarChar).Value = pUser.WorkType;
            sqlCmd.Parameters.Add("@Credential", SqlDbType.VarChar).Value = pUser.Credential;
            sqlCmd.Parameters.Add("@IDCard", SqlDbType.VarChar).Value = pUser.IDCard;
            sqlCmd.Parameters.Add("@IsSafetyEducation", SqlDbType.Int).Value = pUser.IsSafetyEducation;
            sqlCmd.Parameters.Add("@IsAutoDeductPoints", SqlDbType.Int).Value = pUser.IsAutoDeductPoints;
            sqlCmd.Parameters.Add("@AutoDeductPeriod", SqlDbType.Int).Value = pUser.AutoDeductPeriod;
            sqlCmd.Parameters.Add("@AutoDeductPoints", SqlDbType.Int).Value = pUser.AutoDeductPoints;

            if (pUser.AutoDeductDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@AutoDeductDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@AutoDeductDate", SqlDbType.DateTime).Value = pUser.AutoDeductDate;
            }
          
            sqlCmd.Parameters.Add("@IsAutoRecharge", SqlDbType.Int).Value = pUser.IsAutoRecharge;
            sqlCmd.Parameters.Add("@CurrentIntegral", SqlDbType.Int).Value = pUser.CurrentIntegral;
            sqlCmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = pUser.RoleID;
            sqlCmd.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = pUser.UserTypeID;
            sqlCmd.Parameters.Add("@Password", SqlDbType.Char).Value = pUser.Password;
            sqlCmd.Parameters.Add("@IsFine", SqlDbType.Int).Value = pUser.IsFine;
            try
            {
                // open the connection and run the insert command
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : Deletes a User record from the db
        ///Pre-condition : record has to exist in db
        ///Post-condition: none
        ///Return        : true - if record was deleted successfully.
        ///				   false - if record was not deleted;
        /// </summary>
        public bool DeleteUser(int pUserID)
        {
            string sqlDelete = " delete from Staff WHERE UserID = @UserID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            bool pboolResult = false;

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usDeleteUser");
            SqlCommand sqlCmd = new SqlCommand(sqlDelete);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = pUserID;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
                pboolResult = true;
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
            return pboolResult;
        }
    }
}